package com.dys.dao.impl;

import com.dys.dao.StudentDao;
import com.dys.entity.Clazz;
import com.dys.entity.Student;
import com.dys.util.ConnectionPool;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @className: com.dys.dao.impl.StudentDaoImpl
 * @description: com.dys.dao.impl
 * @author: pine cone
 * @version: v1.0.0
 * @createTime: 2024/10/09 22:13
 */
public class StudentDaoImpl implements StudentDao {
    @Override
    public int saveStudentInfo(Student student) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("insert into t_student values(default,?,?,?,?,?,?,?)");
            // 设置预编译参数
            ps.setString(1, student.getSname());
            ps.setString(2, student.getSex());
            ps.setString(3, student.getHobby());
            ps.setDate(4, student.getBirthdate());
            ps.setString(5, student.getPhone());
            ps.setString(6, student.getDesc());
            ps.setInt(7, student.getCid());
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public List<Student> listStudentInfoByNameAndPhone(String studentName, String phone) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();
        try {
            // 预编译SQL
            // 预编译sql:
            String sql = "select * from t_student s join t_class c on s.cid = c.cid where 1=1";
            if (!"".equals(studentName) && studentName != null) {
                sql += " and s.sname like '%" + studentName + "%'";
            }
            if (!"".equals(phone) && phone != null) {
                sql += " and s.phone like '%" + phone + "%'";
            }
            ps = conn.prepareStatement(sql);
            System.out.println(sql);
            // 执行SQL
            rs = ps.executeQuery();
            // 处理结果
            while (rs.next()) {
                // 获取学生相关属性
                int sid = rs.getInt("sid");
                String sname = rs.getString("sname");
                String sex = rs.getString("sex");
                String hobby = rs.getString("hobby");
                Date birthdate = rs.getDate("birthdate");
                String sphone = rs.getString("phone");
                String sdesc = rs.getString("desc");
                // 获取班级相关属性
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String clocation = rs.getString("clocation");
                String desc = rs.getString("desc");
                // 封装班级对象
                Clazz clazz = new Clazz(cid, cname, clocation, desc);
                // 封装学生对象
                Student student = new Student(sid, sname, sex, hobby, birthdate, sphone, sdesc, cid, clazz);
                // 将学生对象添加到集合
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return list;
    }

    @Override
    public int selectStudentCount() {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select count(*) from t_student");
            // 执行SQL
            rs = ps.executeQuery();
            // 获取总记录数
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return count;
    }

    @Override
    public List<Student> selectStudentInfoByPage(int pageNo, int pageSize, String studentName, String phone) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();
        try {
            // 预编译SQL
            // 预编译sql:
            String sql = "select * from t_student s join t_class c on s.cid = c.cid where 1=1";
            if (!"".equals(studentName) && studentName != null) {
                sql += " and s.sname like '%" + studentName + "%'";
            }
            if (!"".equals(phone) && phone != null) {
                sql += " and s.phone like '%" + phone + "%'";
            }
            sql += " limit ?,? ";
            ps = conn.prepareStatement(sql);
            // 设置参数
            ps.setInt(1, pageNo);
            ps.setInt(2, pageSize);
            System.out.println(sql);

            // 执行SQL
            rs = ps.executeQuery();
            // 处理结果
            while (rs.next()) {
                // 获取学生相关属性
                int sid = rs.getInt("sid");
                String sname = rs.getString("sname");
                String sex = rs.getString("sex");
                String hobby = rs.getString("hobby");
                Date birthdate = rs.getDate("birthdate");
                String sphone = rs.getString("phone");
                String sdesc = rs.getString("desc");
                // 获取班级相关属性
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String clocation = rs.getString("clocation");
                String desc = rs.getString("desc");
                // 封装班级对象
                Clazz clazz = new Clazz(cid, cname, clocation, desc);
                // 封装学生对象
                Student student = new Student(sid, sname, sex, hobby, birthdate, sphone, sdesc, cid, clazz);
                // 将学生对象添加到集合
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return list;
    }

    @Override
    public int deleteOneStudentInfo(int sid) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("delete from t_student where sid=?");
            ps.setInt(1, sid);
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public Student selectOneStudentInfo(int sid) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select * from t_student where sid=?");
            ps.setInt(1, sid);
            // 执行SQL
            rs = ps.executeQuery();
            // 获取班级信息，封装对象
            if (rs.next()) {
                int sid1 = rs.getInt("sid");
                String sname = rs.getString("sname");
                String sex = rs.getString("sex");
                String hobby = rs.getString("hobby");
                Date birthdate = rs.getDate("birthdate");
                String sphone = rs.getString("phone");
                String sdesc = rs.getString("desc");
                int cid = rs.getInt("cid");
                student = new Student(sid1, sname, sex, hobby, birthdate, sphone, sdesc, cid);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return student;
    }

    @Override
    public int updateStudentInfo(Student student) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("update t_student set sname=?,sex=?,hobby=?,birthdate=?,phone=?,`desc`=?,cid=? where sid=?");
            ps.setString(1, student.getSname());
            ps.setString(2, student.getSex());
            ps.setString(3, student.getHobby());
            ps.setDate(4, student.getBirthdate());
            ps.setString(5, student.getPhone());
            ps.setString(6, student.getDesc());
            ps.setInt(7, student.getCid());
            ps.setInt(8, student.getSid());
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }
}
